﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Frogism.Core;

namespace Frogism.Data {
  public abstract class LoaderBase<T,I> : ILoader<T,I> where T : ObjectIdBase where I : IObjectIdBase{
    protected virtual string ConnectionStringName() {
      return "FrogismConnectionString";
    }
    protected string ConnectionString {
      get {
        if(null == System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringName()]) {
          throw new FrogismConfigurationException(ConnectionStringName());
        } else {
          return System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringName()].ConnectionString;
        }
      }
    }
    private I FillOne(SqlDataReader reader) {
      return FillOne(FriendlyReader.Create(reader));
    }
    private I FillOne(FriendlyReader reader) {
      I i = GetEmptyObject();
      i.Id = reader.Int("Id");
      FillObject(i, reader);
      return i;
    }
    protected abstract I GetEmptyObject();
    protected abstract void FillObject(I i,FriendlyReader reader);
    protected abstract string ProcNameGetAll { get; }
     protected abstract string ProcNameGetById { get; }
    protected SqlConnection GetConnection() {
       SqlConnection conn = new SqlConnection(ConnectionString);
      conn.Open();
      return conn;
    }

    public IEnumerable<I> GetAll() {
      return ProcedureGetAll(ProcNameGetAll);
    }
    protected Collection<I> ProcedureGetMany(string p, IntegerParameter integerParameter) {
      using (var conn = GetConnection()) {
        var cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = p;
        AddIntParam(cmd, integerParameter);

        var reader = cmd.ExecuteReader();
        return FillAll(reader);

      }
    }
    public  I GetById(int id) {
      return ProcedureGetOne(ProcNameGetById, id);
    }
    protected Collection<I> FillAll(SqlDataReader reader) {
      Collection<I> items = new Collection<I>();
      FriendlyReader fReader = FriendlyReader.Create(reader);
      while(fReader.Read()) {
         items.Add(FillOne(fReader));
      }
      return items;
    }
    protected class StringParameter {
      public string Name { get; set; }
      public string Value { get; set; }
    }
    protected class IntegerParameter {
      public string Name { get; set; }
      public int Value { get; set; }
    }
    protected I SqlGetById(string sql, int id) {

      using(SqlConnection conn = GetConnection()) {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        AddIntParam(cmd, "id", id);
        SqlDataReader reader = cmd.ExecuteReader();
        if(reader.Read()) {
          return FillOne(reader);
        } else {
          return default(I);
        }
      }
    }
    protected Collection<I> SqlGetAll(string sql) {

      using(SqlConnection conn = GetConnection()) {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        SqlDataReader reader = cmd.ExecuteReader();
        return FillAll(reader);
      }
    }
    protected Collection<I> ProcedureGetAll(string procedureName, IntegerParameter parameter) {

      using(SqlConnection conn = GetConnection()) {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = procedureName;
        cmd.CommandType = CommandType.StoredProcedure;
        AddIntParam(cmd, parameter);
        SqlDataReader reader = cmd.ExecuteReader();

        return FillAll(reader);
      }
    }
    protected Collection<I> ProcedureGetAll(string procedureName) {

      using(SqlConnection conn = GetConnection()) {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = procedureName;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader reader = cmd.ExecuteReader();

        return FillAll(reader);
      }
    }
    protected void ProcedureNonQuery(string procedureName, int id) {
      using(SqlConnection conn = GetConnection()) {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = procedureName;
        cmd.CommandType = CommandType.StoredProcedure;
        AddIntParam(cmd, "id", id);
        cmd.ExecuteNonQuery();
      }
    }

    protected I ProcedureGetOne(string procedureName, int id) {

      using(SqlConnection conn = GetConnection()) {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = procedureName;
        cmd.CommandType = CommandType.StoredProcedure;
        AddIntParam(cmd, "id", id);
        SqlDataReader reader = cmd.ExecuteReader();
        FriendlyReader fReader = FriendlyReader.Create(reader);
        if(fReader.Read()) {
          return FillOne(fReader);
        } else {
          return default(I);
        }
      }
    }
    protected static void AddIntParam(SqlCommand cmd, IntegerParameter paramater) {
      AddIntParam(cmd, paramater.Name, paramater.Value);
    }
    protected static void AddDecimalParam(SqlCommand cmd, string paramName, decimal paramValue) {
      SqlParameter p = cmd.CreateParameter();
      p.Direction = ParameterDirection.Input;
      p.SqlDbType = SqlDbType.Decimal;
      p.ParameterName = "@" + paramName;
      p.Value = paramValue;
      cmd.Parameters.Add(p);
    }
    protected static void AddDateParam(SqlCommand cmd, string paramName, DateTime paramValue) {
      SqlParameter p = cmd.CreateParameter();
      p.Direction = ParameterDirection.Input;
      p.SqlDbType = SqlDbType.DateTime;
      p.ParameterName = "@" + paramName;
      p.Value = paramValue;
      cmd.Parameters.Add(p);
    }
    protected static void AddBoolParam(SqlCommand cmd, string paramName, bool paramValue) {
      SqlParameter p = cmd.CreateParameter();
      p.Direction = ParameterDirection.Input;
      p.SqlDbType = SqlDbType.Bit;
      p.ParameterName = "@" + paramName;
      p.Value = paramValue;
      cmd.Parameters.Add(p);
    }
    protected static void AddStringParam(SqlCommand cmd, string paramName, string paramValue) {
      SqlParameter p = cmd.CreateParameter();
      p.Direction = ParameterDirection.Input;
      p.SqlDbType = SqlDbType.VarChar;
      p.ParameterName = "@" + paramName;
      p.Value = paramValue;
      cmd.Parameters.Add(p);
    }
    protected static void AddDoubleParam(SqlCommand cmd, string paramName, Double paramValue) {
      SqlParameter p = cmd.CreateParameter();
      p.Direction = ParameterDirection.Input;
      p.SqlDbType = SqlDbType.Float;
      p.ParameterName = "@" + paramName;
      p.Value = paramValue;
      cmd.Parameters.Add(p);
    }
    protected static void AddIntParam(SqlCommand cmd, string paramName, int paramValue) {
      SqlParameter p = cmd.CreateParameter();
      p.Direction = ParameterDirection.Input;
      p.SqlDbType = SqlDbType.Int;
      p.ParameterName = "@" + paramName;
      p.Value = paramValue;
      cmd.Parameters.Add(p);
    }
      protected void AddNullParam( SqlCommand cmd, SqlDbType paramType, String paramName ){
            SqlParameter p   = cmd.CreateParameter();
            p.Direction = ParameterDirection.Input;
            p.SqlDbType = paramType;
            p.ParameterName = "@" + paramName;
            p.Value = DBNull.Value;
            cmd.Parameters.Add(p);
         }
  }
}
